from backend.database.db_connection import DatabaseConnection

from backend.database.db_connection import DatabaseConnection

class BaseDB:
    def __init__(self, table_name):
        self.table_name = table_name
        self.db = DatabaseConnection()
    
    def execute_query(self, query, params=None):
        cursor = self.db.get_cursor()
        try:
            cursor.execute(query, params or ())
            self.db.commit()
            return cursor
        except Exception as e:
            self.db.rollback()
            raise e
    
    def fetch_one(self, query, params=None):
        cursor = self.execute_query(query, params)
        result = cursor.fetchone()
        cursor.close()
        return result
    
    def fetch_all(self, query, params=None):
        cursor = self.execute_query(query, params)
        results = cursor.fetchall()
        cursor.close()
        return results
    
    def insert(self, data):
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO {self.table_name} ({columns}) VALUES ({placeholders})"
        
        cursor = self.execute_query(query, tuple(data.values()))
        last_id = cursor.lastrowid
        cursor.close()
        return last_id
    
    def insert_many(self, data_list):
        if not data_list:
            return 0
        
        # 获取第一个元素的键作为列名
        columns = ', '.join(data_list[0].keys())
        placeholders = ', '.join(['%s'] * len(data_list[0]))
        query = f"INSERT INTO {self.table_name} ({columns}) VALUES ({placeholders})"
        
        # 准备参数列表
        params_list = [tuple(data.values()) for data in data_list]
        
        cursor = self.db.get_cursor()
        try:
            cursor.executemany(query, params_list)
            self.db.commit()
            affected_rows = cursor.rowcount
            cursor.close()
            return affected_rows
        except Exception as e:
            self.db.rollback()
            cursor.close()
            raise e
    
    def update(self, data, condition):
        set_clause = ', '.join([f"{key} = %s" for key in data.keys()])
        query = f"UPDATE {self.table_name} SET {set_clause} WHERE {condition}"
        
        cursor = self.execute_query(query, tuple(data.values()))
        affected_rows = cursor.rowcount
        cursor.close()
        return affected_rows
    
    def delete(self, condition):
        query = f"DELETE FROM {self.table_name} WHERE {condition}"
        
        cursor = self.execute_query(query)
        affected_rows = cursor.rowcount
        cursor.close()
        return affected_rows
    
    def get_count(self, condition=None):
        if condition:
            query = f"SELECT COUNT(*) as count FROM {self.table_name} WHERE {condition}"
        else:
            query = f"SELECT COUNT(*) as count FROM {self.table_name}"
        
        result = self.fetch_one(query)
        return result['count'] if result else 0